package co.th.helper.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

public class JdbcTemplate<T> {

	private Connection connection;
    private static final String DEFAULT_CONFIG = "config";
	private static final ResourceBundle configBundle = ResourceBundle.getBundle(DEFAULT_CONFIG);
	private static final String URL = configBundle.getString("URL");
	private static final String DB = configBundle.getString("DB");
	private static final String DRIVER = configBundle.getString("DRIVER");
	private static final String USERNAME = configBundle.getString("USERNAME");
	private static final String PASSWORD = configBundle.getString("PASSWORD");
	private static final String CharacterEncoding = "?characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull";
	
	private PreparedStatement stmt = null;
	
	protected String transaction = Calendar.getInstance().toString();
	protected static Logger _logger = Logger.getLogger(JdbcTemplate.class.getClassLoader().getClass());
	
	public T queryUnique(String sql, Object[] params, RowMapper<T> rowMapper) throws SQLException, Exception {
		T results = null;
		ResultSet resultSet = null;
		try {
			resultSet = executeQuery(sql, params);
			if (resultSet.next()) {
				results = rowMapper.mapRow(resultSet);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (resultSet != null){
				resultSet.close();
			}
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return results;
	}

	public List<T> queryList(String sql, Object[] params, RowMapper<T> rowMapper) throws SQLException, Exception {
		List<T> results = new ArrayList<T>(0);
		ResultSet resultSet = null;
		try {
			resultSet = executeQuery(sql, params);
			while (resultSet.next()) {
				results.add(rowMapper.mapRow(resultSet));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (resultSet != null){
				resultSet.close();
			}
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return results;
	}
	
	public int saveUnique(String sql, Object[] params) throws SQLException, Exception {
		int rowOfInsert = 0;
		try {
			rowOfInsert = executeUpdate(sql, params);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return rowOfInsert;
	}
	
	public int saveUnique(String sql, Object[] params, RowMapper<T> rowMapper) throws SQLException, Exception {
		int rowOfInsert = 0;
		try {
			rowOfInsert = executeUpdate(sql, params);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return rowOfInsert;
	}
	
	public int updateUnique(String sql, Object[] params, RowMapper<T> rowMapper) throws SQLException, Exception {
		int rowOfInsert = 0;
		try {
			rowOfInsert = executeUpdate(sql, params);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return rowOfInsert;
	}
	
	public int deleteUnique(String sql, Object[] params, RowMapper<T> rowMapper) throws SQLException, Exception {
		int rowOfInsert = 0;
		try {
			rowOfInsert = executeUpdate(sql, params);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			closeConnection();
		}
		return rowOfInsert;
	}
	
	private ResultSet executeQuery(String sql, Object[] params) throws Exception{
		ResultSet rs = null;
		try{			
			connection = createConnection();
			stmt = connection.prepareStatement(sql);
			if (params != null){
				for(int i=1 ; i<=params.length ; i++){
					setPreparedStatement(stmt, i, params[i-1]);
				}
			}
			rs = stmt.executeQuery();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return rs;
	}
	
	private int executeUpdate(String sql, Object[] params) throws Exception{
		int rowOfExecute = 0;
		try{			
			connection = createConnection();
			stmt = connection.prepareStatement(sql);
			if (params == null){
				throw new Exception("Please set value of param.");
			}
			for(int i=0 ; i<params.length ; i++){
				setPreparedStatement(stmt, (i+1), params[i]);
			}
			rowOfExecute = stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return rowOfExecute;
	}
	
	private void setPreparedStatement(PreparedStatement stmt, int index, Object obj) throws Exception{
		if (obj == null){
			stmt.setObject(index, obj);
		}else if (obj instanceof BigDecimal){
			stmt.setBigDecimal(index, (BigDecimal)obj);
		}else if (obj instanceof String){
			stmt.setString(index, (String)obj);
		}else if (obj instanceof Date){
			Date date = (Date)obj;
			stmt.setDate(index, new java.sql.Date(date.getTime()));
		}else if (obj instanceof java.sql.Blob){
			stmt.setBlob(index, (java.sql.Blob)obj);
		}else{
			throw new Exception("Can not cast varible type.");
		}
	}
	
	private Connection createConnection() throws Exception{
		try{			
			Class.forName(DRIVER).newInstance();
			connection = (Connection) DriverManager.getConnection(URL + DB + CharacterEncoding, USERNAME, PASSWORD);
		}catch(Exception ex ){
			ex.printStackTrace();
			connection = null;
		}
		return connection;
	}
	
	private void closeConnection() throws Exception{
		if (connection != null){
			try {
				if (!connection.getAutoCommit()){
					connection.commit();
				}
				
				if (!connection.isClosed()){
					connection.close();
				}
				connection = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
